<?php
	class empmodel extends Model {
		function empmodel()
		{
			parent::Model();			
		}
		
		function add($data)
		{
			$dataDB = array();
			$dataDB['first_name'] = $data['first_name'];
			$dataDB['last_name'] = $data['last_name'];
			$dataDB['username'] = $data['username'];
			$dataDB['job_position'] = $data['job_position'];
			$dataDB['is_active'] = '1';
			$dataDB['email'] = $data['email'];
			$dataDB['telephone_no'] = $data['telephone_no'];
			$dataDB['schools'] = $data['schools'];
			$this->db->set('create_date', 'NOW()', FALSE); 
			$this->db->set('create_by', "'". $this->session->userdata('username') ."'", FALSE);
			$this->db->insert('employee', $dataDB);
			
			return $this->db->insert_id();
		}
		
		function getPermissionByUsername($id)
		{	
			$this->db->from('employee');
			$this->db->join('employee_school', 'employee_school.employee_id = employee.employee_id', 'left');
			$this->db->where(array('username' => $id));
			//$this->db->where(array('school_id' => '1'));
			$query = $this->db->get();
			
			return $query->row_array();
		}
		
		function getByID($id)
		{
			$query = $this->db->get_where('employee', array('employee_id' => $id));
			return $query->row_array();
		}

		function clearSchool($id)
		{	
			$dataDB = array();
            $this->db->where('employee_id', $id);
            $this->db->set('a', "'N'", FALSE);
			$this->db->set('b', "'N'", FALSE);
			$this->db->set('c', "'N'", FALSE);
			$this->db->set('d', "'N'", FALSE);
			$this->db->set('e', "'N'", FALSE);            
            $this->db->set('update_date', 'NOW()', FALSE); 
            $this->db->set('update_by', "'". $this->session->userdata('username') ."'", FALSE);
			$this->db->update('employee', $dataDB); 
		}
		
		function updateSchool($id, $schoolname)
		{	
			$dataDB = array();
			$dataDB[$schoolname] = 'Y';
            $this->db->where('employee_id', $id);
            $this->db->set('update_date', 'NOW()', FALSE); 
            $this->db->set('update_by', "'". $this->session->userdata('username') ."'", FALSE);
			$this->db->update('employee', $dataDB); 
		}
		
		function updateInfo($data)
		{	
			$dataDB = array();
			$dataDB['first_name'] = $data['first_name'];
			$dataDB['last_name'] = $data['last_name'];
			$dataDB['username'] = $data['username'];	
			$dataDB['job_position'] = $data['job_position'];
			$dataDB['email'] = $data['email'];
			$dataDB['telephone_no'] = $data['telephone_no'];
			$dataDB['schools'] = $data['schools'];
            $this->db->where('employee_id', $data['employee_id']);
            $this->db->set('update_date', 'NOW()', FALSE); 
            $this->db->set('update_by', "'". $this->session->userdata('username') ."'", FALSE);
			$this->db->update('employee', $dataDB); 
		}
		
		function delete($data)
		{	
			$dataDB['is_active'] = '0';	
            $this->db->where('employee_id', $data['employee_id']);
            $this->db->set('update_date', 'NOW()', FALSE); 
            $this->db->set('update_by', "'". $this->session->userdata('username') ."'", FALSE);
			$this->db->update('employee', $dataDB); 
		}
		
		function getAll()
		{
			$this->db->order_by("first_name", "asc"); 
			$query = $this->db->get('employee');
			return $query->result();
		}
		
		function getByCriteria($key)
		{
			$sql = "SELECT em.*, GROUP_CONCAT( sc.school_name ) as 'school_name'  
FROM employee em, users u, employee_school tusc , school sc 
where  em.username = u.username AND tusc.school_id = sc.school_id  
and u.profile = 'employee' and em.is_active = '1' and tusc.employee_id = em.employee_id ";
			 
			if($key['txtName'] != null) {
				$sql .= " AND ( em.first_name LIKE '%" . $key['txtName'] . "%' or  em.last_name LIKE '%" . $key['txtName'] . "%' )";
			}
			if($key['txtUsername'] != null) {
				$sql .= " and em.username LIKE '%" . $key['txtUsername'] . "%' ";
			}		
			$sql .= " GROUP BY em.employee_id order by first_name, last_name  ";		
			$query = $this->db->query($sql);  
			return $query->result();
		}
	
	
			function empSearch($key)
		{
//			$this->output->enable_profiler(TRUE);
			$sql = " select em.*, GROUP_CONCAT( sc.school_name ) as 'school_name'  "
		        . ' FROM employee em
				left join employee_school tusc on em.employee_id = tusc.employee_id
				left join school sc on  tusc.school_id = sc.school_id  ';
		        
			if($key['name'] != null)
				$sql .= " and (first_name like '%".$key['name']."%' or last_name like '%".$key['name']."%' )";
			if($key['tel'] != null)
				$sql .= " and (telephone_no like '%".$key['tel']."%' )";	
			if($key['email'] != null)
				$sql .= " and (email like '%".$key['email']."%' )";
			$sql .= " and em.is_active = '".$key['emp_status']."'";
			if($key['job_position'] != null)
		    	$sql .= " and job_position like '%" . $key['job_position'] . "%' "  ;
			if($key['school'] != null)
		    	$sql .= $key['school'] ;	
				
			$sql .= " GROUP BY em.username order by first_name, last_name ";	
			$query = $this->db->query($sql);  
			return $query->result();
		}
	}
?>